<?php

namespace iflow\annotation\Db\Adapter\Pgsql;

use iflow\annotation\Db\Adapter\Abstracts\CreateTableAbstract;
use iflow\annotation\Db\Adapter\Trait\DiffTableTrait;
use iflow\annotation\Db\Column\ColumnIndexType;
use iflow\annotation\Db\Column\ColumnType;
use iflow\facade\DB;
use think\helper\Arr;

class CreateTable extends CreateTableAbstract {

    use DiffTableTrait;

    protected function getCreateTableSql(array $table_structure): string {
        // TODO: Implement getCreateTableSql() method.

        $table = array_change_key_case($table_structure['table'], CASE_UPPER);
        $table['TABLESPACE'] ??= 'pg_default';

        Arr::forget($table, [
            'NAME', 'ENGINE', 'DEFAULT', 'CHARSET', 'COLLATE', 'COMMENT', 'COLLATION'
        ]);

        return sprintf(
            "CREATE TABLE IF NOT EXISTS %s (\n%s\n) %s;",
            $this->table,
            implode(',' . PHP_EOL, array_map(
                fn ($column) => $this -> columnSql($column), $table_structure['columns']
            )),
            implode(' ', array_map(fn ($key) => "$key $table[$key]", array_keys($table)))
        );
    }


    public function handle(array $options): bool {
        parent::handle($options); // TODO: Change the autogenerated stub

        // 增加注释
        $this->connectHandle -> execute(
            "COMMENT ON TABLE {$this -> table} IS '{$options['table_structure']['table']['comment']}'"
        );
        $this -> addColumnsComments($options['table_structure']['columns']);

        return true;
    }

    public function columnSql(array $column): string {
        return sprintf(
            '%s %s %s %s %s',
            $column['name'],
            $column['primaryKey'] ? '' : $this -> getTypeLength($column['type'], $column['length']),
            $this -> getPrimaryColumnType($column),
            !$column['nullable'] ? ' NOT NULL' : '',
            $column['defaultValue'] ? ' DEFAULT \'' . $column['defaultValue'] . '\'' : ($column['nullable'] ? 'NULL' : '')
        );
    }


    public function getPrimaryColumnType(array $column): string {
        // TODO: Implement getPrimaryColumnType() method.
        if (!$column['primaryKey']) return '';

        return sprintf(
            '%s PRIMARY KEY',
            $column['autoIncrement'] ? 'BIGSERIAL' : ColumnType::BIGINT
        );
    }

    protected function getTypeLength(string $type, int $length): string {
        // TODO: Implement getTypeLength() method.
        $type = strtoupper($type);

        if ($type === ColumnType::TINYINT) return ColumnType::SMALLINT;

        if ($length === 255 && !in_array($type, [ ColumnType::PG_VARYING, ColumnType::STRING ]))
            return $type;

        return "$type($length)";
    }

    public function checkTableExists(string $table, string $connection): bool {
        // TODO: Implement checkTableExists() method.
        $result = DB::query("SELECT 1 as _exists FROM pg_tables WHERE tablename=:table", [ 'table' => $table ]);
        if (count($result) > 0) return $result[0]['_exists'];
        return false;
    }

    public function removeColumn(string $table, array $column): bool {
        // TODO: Implement removeColumn() method.
        return $this->connectHandle -> execute(
            sprintf("ALTER TABLE %s DROP COLUMN %s", $table, $column['name'])
        );
    }

    public function addColumn(string $table, array $column): bool {
        // TODO: Implement addColumn() method.
        $result = $this->connectHandle->query(
            "SELECT COUNT(*) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '{$table}') AND attname = '{$column['name']}'"
        );
        if ($result[0]['count'] > 0) return true;

        return $this->connectHandle -> execute(
            sprintf("ALTER TABLE %s ADD COLUMN %s", $table, $this -> columnSql($column))
        );
    }

    public function modifyColumn(string $table, array $column): bool {
        // TODO: Implement modifyColumn() method.
        return $this->connectHandle -> execute(
            sprintf("ALTER TABLE %s ALTER COLUMN %s", $table, $this -> columnSql($column))
        );
    }

    public function removeIndex(string $table, array $index): bool {
        // TODO: Implement removeIndex() method.
        return $this->connectHandle -> execute("DROP INDEX {$index['index_name']}");
    }

    public function addIndex(string $table, array $index): bool {
        // TODO: Implement addIndex() method.

        if ($index['index_type'] === ColumnIndexType::PRIMARY) return true;
        $sql = sprintf(
            "CREATE %s %s ON %s(%s)",
            ($index['index_type'] === ColumnIndexType::INDEX ? '' : $index['index_type']) .' '. ColumnIndexType::INDEX,
            $index['index_name'], $table, $index['column_name']
        );

        if ($index['index_type'] === ColumnIndexType::FOREIGN) {
            $sql = sprintf(
                "ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)",
                $table, $index['index_name'], $index['column_name'],
                $index['references_table'], $index['references']
            );
        }

        return $this->connectHandle -> execute($sql);
    }


    public function addColumnsComments(array $columns): CreateTable {
        foreach ($columns as $column) {
            $this->connectHandle -> execute(
                "COMMENT ON COLUMN {$this->table}.{$column['name']} IS '{$column['description']}'"
            );
        }
        return $this;
    }
}